Best Practice Guide - Implementing an Intra-Day Data Strategy

• All data load activity is carried out by iLoader.
• Users are familiar and experienced in the use of iLoader, especially in terms of UPDATE_ONLY, UPDATE and APPEND functionality.
• Both Intra-Day and overnight loads will go through all Alterian data load best practices i.e. system shutdown prior to load.
• Users have a working knowledge of the Campaign Manager architecture, including Engine, and the underlying Campaign State data structure.
• AMC users should not perform actions that require Engine to be restarted if any other AMC users are connected, as this will cause an unhandled exception in Engine. Look in the cerlog to see if any other AMC users are connected.
• Failure to use this functionality without first reviewing this document has the potential to seriously disrupt campaign activity and cause data corruption.

• The success of an Intra-Day load strategy does rely on some key processes and compromises in terms of the implementation.
• Intra-Day load failures may leave the repository in an inconsistent state and with the system remaining on-line this will impact users. To mitigate this risk, the Intra-Day load should perform minimal processing and be carefully monitored.
• This is NOT a replacement for an overnight load to facilitate loading of the wider dataset. This process must be used sparingly and only on tables that are necessary.
• User must be educated on how an Intra-Day update strategy will impact Campaign Manager usage and on best practice with respect to audience refresh settings.
• Users will be informed on the engineering approach to ensure they adhere to the framework implemented.
• Currently, in-place updates are supported where <=1,000,000 rows are updated as beyond this, it becomes too restrictive from a performance perspective.

Column Indexing

As data is loaded, the default option is to load all fields indexed. Indexing has a performance overhead and to load the data as quickly as possible, a review of the indexing strategy is required, especially on the tables that will receive Intra-Day updates.

The following should be considered when looking at column indexes on tables:

• Campaign Key must be indexed.
• Primary link keys must be indexed.
• For reporting, High Cardinality fields (>32,000 discrete values) of any type, that will be used in a crosstab, must be indexed or the crosstab will fail.
• For querying, High Cardinality fields (>32,000 discrete values) of any data type, should only be indexed where is it necessary to see values in the helpers or columns that will be regularly queried.
• Low Cardinality (< 32,000 discrete values) columns do not need indexing for crosstabs, querying or value visibility. Query performance will improve query speed but the overhead of indexing may be considered more impacting.

For new tables, to load fields as unindexed, the iLoader script data type is prefixed with a “U”.

For Example:

BEGIN TABLE_DEFINITION

Id, INTEGER, 00 , 10

Title, TEXT       , 01 , 15

FirstName, UTEXT     , 02 , 40

LastName, UTEXT     , 03 , 40    Email, UTEXT     , 04 , 241

END TABLE_DEFINITION

For existing tables, the field can be set as unindexed via AMC -> Engine Repository.

System House Keeping

Any updates to the campaign key table will cascade a maintenance of links between it and all the child tables, including the campaign state tables. It is extremely important that the number of campaign state tables are monitored.

The State table retention policy can found in the Datasource configuration screen:

Additional benefits are will also be seen in campaign processing, pausing of campaigns and schema refreshes in Campaign Manager

Campaign End Dates - Users must be educated about the implications of not setting the campaign end date, as this will lead to many campaign state tables being unnecessarily retained. Default End date is the same day as start for this reason.

Test Campaigns Users must be educated about the implications of leaving campaigns running in test mode, unnecessary state tables and objects will be retained in the _TEST_TacticOutputTables database. Where applicable, users should end all test campaigns and clear the state tables. Tables stored in the _TEST_TacticOutputTables database should be routinely removed. Additional benefits will also be seen in campaign processing, pausing of campaigns and schema refreshes in Campaign Manager.

During the Intra-Day loads, tables will be locked and as such could have a cascading affect onto campaigns and would cause a one-off failure in Engines ability to process the audience so it is strongly recommended that campaigns are therefore paused and resumed during the Intra-Day load process, although it is not necessary to log all users off or disable data source. The pausing of campaigns does not affect the use of Campaign Manager for end users. They will be able to create new reports, queries, campaigns etc. but as noted, may get error messages when trying to execute them, if the underlying tables are locked during the data load.

Alterian would stress that users should be made aware of the frequency of these updates and best practice would be to keep frequency to a minimum.

Using existing iLoader best practices, an Intra-Day load to a table may now be performed, but again it is important to stress, that to minimize the Intra-Day load impact, this process must load a minimal set of fields and tables required for campaigns.

Tables should be loaded as either updates (update and insert) or append only. Alterian recommend that Clients start with a critical subset. Once users start to create campaigns and the system reflects typical operation use, the system is replicated to a test environment and additional fields and tables can be tested.

The frequency of Intra-Day loads will be determined by how quickly, Clients/Partners can supply the source data, but expectations should be set, that we are envisaging Intra-Day loads to run every 2-3 hours and not in the region of minutes which would likely be impractical with respect to time to pause/resume campaigns and impact on users.

Data engineering should not be included in Intra-Day loads unless absolutely necessary as these columns will have to be completely re-created. Considerations should be given to existing engineering columns as altered above.

Once the data load is complete, campaigns can be re-started.

This scenario will undoubtedly have added complexity on a case-by-case basis, and as such, this section will be more in the way of a discussion on capabilities and considerations, rather than an outright solution. Contact the Alterian Professional Services team if assistance is required.

This scenario enters into an area previously considered as out of scope with Engine, that is, adding a new row to a table during business as usual campaign activity.

The iLoader Best Practice - Strategies for Loading Data Guide makes it clear that a Customer table would likely not be a candidate for Intra-Day rows appends with Engine. The main reason for this is the high volume of Data Engineering and Links that exist related to the Customer table.

The adding of a relatively small number of rows Intra-Day in a batched method to the table itself would likely be very possible, but the subsequent invalidation of the dependent objects like engineering and links, would probably make this impractical and ill advised.

Based on this, there is one recommended method to achieve this.

That method is detailed in the following bullet points, but it must be stressed this is an overview and not a defined process, as variations in data and requirements may alter or expand this.

• At the start of each day, create an empty ‘Daily’ customer table containing columns to be populated by the new Customer sign-up events.
• On sign up, there has to be a primary key. When the Customer is added to the ‘Daily’ table, it has a unique Key for campaign activity. Campaign activity related to new Customers is then able to be carried out on the Daily table.
• Sign-Ups are collected in a file-based storage, and appended to the ‘Daily’ Customer table.
• Derived columns based on the data have to be managed accordingly (see Best Practice Guide - Data Engineering in Campaign Manager and Engine, for further information).
• Campaign audience refreshes occur within Campaign Manager and the application would add new Customers to campaign recipients for the Welcome campaign.
• The Campaign activity would produce _TacticOutputTables as per normal process.
• As part of the overnight process, the ArchiveTables command would be used and this creates an independent ‘Daily’ Campaign History table for the new campaign key.
• If required, this new ‘Daily’ Campaign History could then be merged with the production campaign history.
• Event processing could still occur against the Welcome campaign.
• Events would be processed and stored in SQL, the bcp export would produce a set of files for the new campaign key.
• Event files could be processed and created in the appropriate table.

The following questions could affect the decision made:

1. Campaign Key – Will the key provided to a Customer on registration, be the same key that they will carry forward and use for their campaign activity going forward?

It is important to remember that the unique ‘HistoryID’ column used as a key on the Campaign History tables is a concatenation of Campaign_Key and CreativeInstanceID. The CreativeInstanceID will be unique across keys but if the Campaign_Key has different values this would need to be matched to the Customer URN created by the data Warehouse to fully qualify the contact to a recipient.

2. Single Customer View – If there is any single Customer view or post sign-up processing that needs to occur prior to communication with the Customer, would these have to be handled?
3. How is the Customer record built? If the Customer record in the main Customer table comes from the data Warehouse system, as opposed to the Web Sign-up system, then thought should be given as to what will happen to the ‘Daily’ Customer table. That is will it be discarded, appended to the main Customer table or have validation that the new record in the main Customer table matches prior to deletion of the ‘Daily’ record.

1. Can I append high volumes of data to a transaction type table Intra-Day while other users are actively querying that table?

No. The Intra-Day updates will not cope with high volume appends to already large tables due to the level of indexing and data engineering that would have to be executed after the append.

2. Can I update my Customer Table during the day reliably?

This will be dependent on the number of customers in the table and the amount of data engineering. The best approach is likely to be an update to a separate table.

3. Do I need to use Intra-Day Updates if I want to response to Web Activity or Call Center activity?

Not necessarily. Campaign Manager has a flexible event processing engine that can take event data, such as a dropped basket, including the details of the items in the basket, or even a product purchase, and react to that and respond to a known Customer without the need to append that data to an Engine table first.